import { Service } from 'egg';

import { Project } from '../model/Project';

import { GradeRule } from '../model/Grade_rule';

export default class ProjectService extends Service {
    /**
     * 创建考核项目
     * @param param 
     */
    public async createProject(param: any) {
        param.project_name = this.ctx.helper.trim(param.project_name);
        const checkResult: any = await this.ctx.model.Project.findOne<Project>({ attributes: ['project_id'], where: { project_name: param.project_name, is_delete: '0' } });
        if (checkResult) {
            throw new Error(JSON.stringify({ code: 2, msg: '该项目名称已存在，创建失败' }));
        }
        const checkDate: any = await this.checkDate({ project_id: '', project_start_time: param.project_start_time });
        if (!checkDate) {
            throw new Error(JSON.stringify({ code: 2, msg: '项目开始时间必须大于其他项目的结束时间' }));
        }
        const submitData: any = {};
        //生成主键id
        submitData.project_id = this.ctx.helper.genUUID();
        submitData.project_name = param.project_name;
        submitData.project_rule = param.project_rule;
        submitData.project_min = Number(param.project_min);
        submitData.project_max = Number(param.project_max);
        submitData.project_is_self = param.project_is_self;
        submitData.project_start_time = param.project_start_time+' 00:00:00';
        submitData.project_end_time = param.project_end_time + ' 23:59:59';
        submitData.project_status = '1';
        submitData.created_by = param.admin_id;
        submitData.is_delete = '0';

        const employee_id = param.employee_id.split(',');
        const target_employee_id = param.target_employee_id.split(',');
        const position_list = JSON.parse(param.position_list);

        const employeeArr: any = [];
        for (let i = 0; i < employee_id.length; i++) {
            const item: any = {};
            //生成主键
            item.grade_employee_id = this.ctx.helper.genUUID();
            item.project_id = submitData.project_id;
            item.employee_id = employee_id[i];
            item.created_by = param.admin_id;
            item.is_delete = '0';
            employeeArr.push(item);
        }

        const target_employee: any = [];
        for (let i = 0; i < target_employee_id.length; i++) {
            const item: any = {};
            //生成主键
            item.grade_target_id = this.ctx.helper.genUUID();
            item.project_id = submitData.project_id;
            item.employee_id = target_employee_id[i];
            item.created_by = param.admin_id;
            item.is_delete = '0';
            target_employee.push(item);
        }

        const position: any = [];
        for (let i = 0; i < position_list.length; i++) {
            const item: any = {};
            //生成主键
            item.grade_rule_id = this.ctx.helper.genUUID();
            item.project_id = submitData.project_id;
            item.position_id = position_list[i].position_id;
            item.project_score = Number(position_list[i].project_score);
            item.created_by = param.admin_id;
            item.is_delete = '0';
            position.push(item);
        }
        //新建事务
        const tran = await this.ctx.model.transaction();
        try {
            await this.ctx.model.Project.create(submitData, { transaction: tran });
            if (employeeArr.length > 0) {
                await this.ctx.model.GradeEmployee.bulkCreate(employeeArr, { transaction: tran });
            }
            if (target_employee.length > 0) {
                await this.ctx.model.GradeTarget.bulkCreate(target_employee, { transaction: tran });
            }
            if (position.length > 0) {
                await this.ctx.model.GradeRule.bulkCreate(position, { transaction: tran });
            }
            await tran.commit();
        } catch (e) {
            await tran.rollback();
            this.ctx.logger.error(e);
            throw new Error(JSON.stringify({ code: 2, msg: '创建失败' }));
        }
    }

    /**
     * 查找考核项目
     * @param param 
     */
    public async searchProject(param: any) {
        try {
            await this.startProject();
            await this.endProject();
            const page = Number(param.page);
            const size = Number(param.size);
            const limit = (page - 1) * size;
            const status = param.status;
            let queryStr = `select project_id,project_name,project_start_time,project_end_time,project_status from t_project where is_delete='0'`;
            if (status) {
                queryStr += ` and project_status=:status`;
            }
            queryStr += ' order by created_at desc';
            const queryCount = queryStr;
            queryStr += ` limit ${limit},${size}`;
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { status }
            });
            const count: any = await this.ctx.model.query(queryCount, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { status }
            })
            if (result.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            const project_list: any = [];
            for (let i = 0; i < result.length; i++) {
                const item: any = {};
                const project_id = result[i].project_id;
                item.project_id = project_id;
                item.project_name = result[i].project_name;
                item.project_start_time = result[i].project_start_time;
                item.project_end_time = result[i].project_end_time;
                item.project_status = result[i].project_status;
                const queryEmployee=`select g.employee_id from t_grade_employee g inner join t_employee e on g.employee_id=e.employee_id and e.is_delete='0' and e.employee_status='2'
                 where g.project_id=:project_id and g.is_delete='0'`;
                const resultEmployee:any=await this.ctx.model.query(queryEmployee,{
                    type:this.ctx.model.QueryTypes.SELECT,
                    replacements:{project_id}
                })
                const queryGradeResult = `select g.employee_id from t_grade_result g inner join t_employee e on g.employee_id=e.employee_id and e.is_delete='0' and e.employee_status='2' 
                where g.project_id=:project_id and g.is_delete='0' group by g.employee_id having sum(g.grade_result_score) >0`;
                const gradeResultEmployee: any = await this.ctx.model.query(queryGradeResult, {
                    type: this.ctx.model.QueryTypes.SELECT,
                    replacements: { project_id }
                })
                const surplus = resultEmployee.length - gradeResultEmployee.length;
                const schedule = (gradeResultEmployee.length / resultEmployee.length) * 100;
                const temp = schedule.toString().split('.');
                let temp1 = '0';
                if (temp.length > 1) {
                    temp1 = temp[0] + '.' + temp[1].substr(0, 2);
                } else {
                    temp1 = temp[0];
                }
                item.schedule = Number(temp1);
                item.surplus = surplus;
                project_list.push(item);
            }
            const returnData: any = { count: count.length, project_list };
            return returnData;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 根据项目id查找项目信息
     * @param param 
     */
    public async searchProjectById(param: any) {
        try {
            const project_id = param.project_id;
            const projectResult: any = await this.ctx.model.Project.findOne<Project>({
                attributes: ['project_id', 'project_name', 'project_rule', 'project_min', 'project_max', 'project_is_self', 'project_start_time', 'project_end_time', 'project_status'],
                where: { project_id, is_delete: '0' }
            });
            if (!projectResult) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            const queryEmployee=`select g.employee_id from t_grade_employee g inner join t_employee e on g.employee_id=e.employee_id and e.employee_status='2' and e.is_delete='0' 
            where g.project_id=:project_id and g.is_delete='0'`;
            const employee: any = await this.ctx.model.query(queryEmployee,{
                type:this.ctx.model.QueryTypes.SELECT,
                replacements:{project_id}
            });
            const queryTargetEmployee=`select g.employee_id from t_grade_target g inner join t_employee e on g.employee_id=e.employee_id and e.employee_status='2' and e.is_delete='0' 
            where g.project_id=:project_id and g.is_delete='0'`;
            const target_employee: any = await this.ctx.model.query(queryTargetEmployee,{
                type:this.ctx.model.QueryTypes.SELECT,
                replacements:{project_id}
            });
            const grade_rule: any = await this.ctx.model.GradeRule.findAll<GradeRule>({
                attributes: ['position_id', 'project_score'],
                where: { project_id, is_delete: '0' }
            })
            const returnData: any = {};
            returnData.project_id = projectResult.project_id;
            returnData.project_name = projectResult.project_name;
            returnData.project_rule = projectResult.project_rule;
            returnData.project_min = projectResult.project_min;
            returnData.project_max = projectResult.project_max;
            returnData.project_is_self = projectResult.project_is_self;
            returnData.project_start_time = projectResult.project_start_time;
            returnData.project_end_time = projectResult.project_end_time;
            returnData.project_status = projectResult.project_status;
            returnData.employee = employee;
            returnData.target_employee = target_employee;
            returnData.position_list = grade_rule;
            return returnData;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 修改项目
     * @param param 
     */
    public async updateProject(param: any) {
        try {
            param.project_name = this.ctx.helper.trim(param.project_name);
            const queryStr = `select project_id from t_project where project_name=:project_name and is_delete='0' and project_id not in (:project_id)`;
            const checkResult: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { project_name: param.project_name, project_id: param.project_id }
            });
            if (checkResult.length > 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '该项目名称已存在，创建失败' }));
            }
            const submitData: any = {};
            const project_id = param.project_id;
            submitData.project_name = param.project_name;
            submitData.project_rule = param.project_rule;
            submitData.project_min = Number(param.project_min);
            submitData.project_max = Number(param.project_max);
            submitData.project_is_self = param.project_is_self;
            submitData.project_start_time = param.project_start_time+ ' 00:00:00';
            submitData.project_end_time = param.project_end_time + ' 23:59:59';
            submitData.project_status = '1';
            submitData.updated_by = param.admin_id;
            submitData.is_delete = '0';

            const employee_id = param.employee_id.split(',');
            const target_employee_id = param.target_employee_id.split(',');
            const position_list = JSON.parse(param.position_list);

            const employeeArr: any = [];
            for (let i = 0; i < employee_id.length; i++) {
                const item: any = {};
                //生成主键
                item.grade_employee_id = this.ctx.helper.genUUID();
                item.project_id = project_id;
                item.employee_id = employee_id[i];
                item.created_by = param.admin_id;
                item.is_delete = '0';
                employeeArr.push(item);
            }

            const target_employee: any = [];
            for (let i = 0; i < target_employee_id.length; i++) {
                const item: any = {};
                //生成主键
                item.grade_target_id = this.ctx.helper.genUUID();
                item.project_id = project_id;
                item.employee_id = target_employee_id[i];
                item.created_by = param.admin_id;
                item.is_delete = '0';
                target_employee.push(item);
            }

            const position: any = [];
            for (let i = 0; i < position_list.length; i++) {
                const item: any = {};
                //生成主键
                item.grade_rule_id = this.ctx.helper.genUUID();
                item.project_id = project_id;
                item.position_id = position_list[i].position_id;
                item.project_score = Number(position_list[i].project_score);
                item.created_by = param.admin_id;
                item.is_delete = '0';
                position.push(item);
            }
            const queryGradeResultTarget=`select target_employee_id from t_grade_result where project_id=:project_id group by target_employee_id`;
            const gradeResultTarget:any=await this.ctx.model.query(queryGradeResultTarget,{
                type:this.ctx.model.QueryTypes.SELECT,
                replacements:{project_id}
            });
            const queryGradeResultEmployee=`select employee_id from t_grade_result where project_id=:project_id group by employee_id`;
            const gradeResultEmployee:any=await this.ctx.model.query(queryGradeResultEmployee,{
                type:this.ctx.model.QueryTypes.SELECT,
                replacements:{project_id}
            });
            //新建事务
            const tran = await this.ctx.model.transaction();
            try {
                await this.ctx.model.Project.update(submitData, { where: { project_id }, transaction: tran });
                await this.ctx.model.GradeEmployee.destroy({ where: { project_id }, transaction: tran });
                await this.ctx.model.GradeTarget.destroy({ where: { project_id }, transaction: tran });
                await this.ctx.model.GradeRule.destroy({ where: { project_id }, transaction: tran });
                if (employeeArr.length > 0) {
                    await this.ctx.model.GradeEmployee.bulkCreate(employeeArr, { transaction: tran });
                }
                if (target_employee.length > 0) {
                    await this.ctx.model.GradeTarget.bulkCreate(target_employee, { transaction: tran });
                }
                if (position.length > 0) {
                    await this.ctx.model.GradeRule.bulkCreate(position, { transaction: tran });
                }
                for(let i=0;i<gradeResultEmployee.length;i++){
                    let hasData=false;
                    for(let j=0;j<employeeArr.length;j++){
                        if(gradeResultEmployee[i].employee_id===employeeArr[j].employee_id){
                            hasData=true;
                            break;
                        }
                    }
                    if(!hasData){
                        await this.ctx.model.GradeResult.destroy({where:{project_id,employee_id:gradeResultEmployee[i].employee_id},transaction:tran});
                    }
                }
                for(let i=0;i<gradeResultTarget.length;i++){
                    let hasData=false;
                    for(let j=0;j<target_employee.length;j++){
                        if(gradeResultTarget[i].target_employee_id===target_employee[j].employee_id){
                            hasData=true;
                            break;
                        }
                    }
                    if(!hasData){
                        await this.ctx.model.GradeResult.destroy({where:{project_id,target_employee_id:gradeResultTarget[i].target_employee_id},transaction:tran});
                    }
                }
                await tran.commit();
            } catch (e) {
                await tran.rollback();
                this.ctx.logger.error(e);
                throw new Error(JSON.stringify({ code: 2, msg: '修改失败' }));
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 启动和暂停项目
     * @param param 
     */
    public async startAndStopProject(param: any) {
        try {
            const project_id = param.project_id;
            const admin_id = param.admin_id;
            const type = param.type;
            let status = '';
            if (type == 1) {
                status = '2';
            } else if (type == 2) {
                status = '3';
            } else {
                throw new Error(JSON.stringify({ code: 2, msg: '无法识别的操作类型' }));
            }
            const result: any = await this.ctx.model.Project.update({ project_status: status, updated_by: admin_id }, { where: { project_id } });
            if (result[0] <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '操作失败' }));
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找前十名以及最高分，最低分，平均分
     * @param param 
     */
    public async searchTopTenAndAvg(param: any) {
        try {
            const project_id = param.project_id;
            const queryTopTen = `
                    SELECT
                        e.employee_id,
                        e.employee_name,
                        e.employee_head_portrait,
                        w.work_unit_name,
                        temp.score 
                    FROM
                        t_employee e
                        INNER JOIN (
                    SELECT
                        *
                    FROM
                        ( SELECT target_employee_id, sum( grade_result_score ) AS score FROM t_grade_result WHERE project_id = 
                        :project_id AND is_delete = '0' GROUP BY target_employee_id ) temp 
                    ORDER BY
                        score LIMIT 0,10
                        ) temp ON e.employee_id = temp.target_employee_id AND e.is_delete = '0'
                        INNER JOIN t_work_unit w ON e.work_unit_id = w.work_unit_id 
                        AND w.is_delete = '0' ORDER BY temp.score DESC`;
            const queryScore = `
                    SELECT
                        sum( score ) AS total,
                        max( score ) AS max,
                        min( score ) AS min,
                        avg( score ) AS avg,
                        count( target_employee_id ) AS count 
                    FROM
                        ( SELECT target_employee_id, sum( grade_result_score ) AS score FROM t_grade_result WHERE project_id = 
                        :project_id AND is_delete = '0' GROUP BY target_employee_id ) temp`;
            const count = await this.ctx.model.GradeTarget.count({ where: { project_id, is_delete: '0' } });
            const topTen: any = await this.ctx.model.query(queryTopTen, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { project_id }
            });
            if (topTen.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '还没有人打分哟！' }));
            }
            const score: any = await this.ctx.model.query(queryScore, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { project_id }
            });
            const actualCount = score[0].count;
            const total = Number(score[0].total);
            const max = score[0].max;
            let min = score[0].min;
            let avg = score[0].avg;
            if (actualCount < count) {
                min = 0;
                avg = total / count;
            }
            let avgArr = avg.toString().split('.');
            let avg1 = avgArr[0];
            let avg2 = "0";
            if (avgArr.length > 1) {
                avg2 = avgArr[1];
                avg2 = avg2.substr(0, 1);
            }
            avg = avg1 + '.' + avg2;
            const returnData: any = {};
            returnData.topTenList = topTen;
            returnData.max = max;
            returnData.min = min;
            returnData.avg = avg;
            return returnData;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找被评分人列表
     * @param param 
     */
    public async searchTargetEmployeeList(param: any) {
        try {
            const project_id = param.project_id;
            const page = Number(param.page);
            const size = Number(param.size);
            const limit = (page - 1) * size;
            const submitData: any = {};
            submitData.project_id = project_id;
            let queryStr = `
            SELECT * FROM (SELECT
                e.employee_id,
                e.employee_name,
                e.employee_account,
                e.created_at,
                ifnull( temp.score, 0 ) AS score,
                w.work_unit_id,
                w.work_unit_name,
                d.department_id,
                d.department_name,
                p.position_id,
                p.position_name 
            FROM
                t_grade_target g
                LEFT JOIN ( SELECT target_employee_id, sum( grade_result_score ) AS score FROM t_grade_result WHERE project_id = :project_id AND is_delete = '0' GROUP BY target_employee_id ) temp ON g.employee_id = temp.target_employee_id
                INNER JOIN t_employee e ON g.employee_id = e.employee_id 
                AND e.is_delete = '0' AND e.employee_status='2' 
                INNER JOIN t_work_unit w ON e.work_unit_id = w.work_unit_id 
                AND w.is_delete = '0'
                INNER JOIN t_department d ON e.department_id = d.department_id 
                AND d.is_delete = '0'
                INNER JOIN t_position p ON e.position_id = p.position_id 
                AND p.is_delete = '0' 
            WHERE
                g.is_delete = '0' 
                AND g.project_id = :project_id) temp WHERE 1=1`;
            if (param.work_unit_id) {
                queryStr += ` AND work_unit_id=:work_unit_id`;
                submitData.work_unit_id = param.work_unit_id;
            }
            if (param.department_id) {
                queryStr += ` AND department_id=:department_id`;
                submitData.department_id = param.department_id;
            }
            if (param.position_id) {
                queryStr += ` AND position_id=:position_id`;
                submitData.position_id = param.position_id;
            }
            if (param.register_start_time && param.register_end_time) {
                queryStr += ` AND created_at between :register_start_time AND :register_end_time`;
                submitData.register_start_time = param.register_start_time;
                submitData.register_end_time = param.register_end_time;
            }
            if (param.employee_name) {
                queryStr += ` AND employee_account like :employee_name OR employee_name like :employee_name`;
                submitData.employee_name = '%' + param.employee_name + '%';
            }
            let queryCount = '';
            if (param.field && param.order) {
                if (param.order === 'ascend') {
                    if (param.field === 'score') {
                        queryCount = queryStr + ` ORDER BY score ASC`;
                        queryStr += ` ORDER BY score ASC LIMIT ${limit},${size}`;
                    } else if (param.field === 'work_unit_name') {
                        queryCount = queryStr + ` ORDER BY work_unit_name ASC`;
                        queryStr += ` ORDER BY work_unit_name ASC LIMIT ${limit},${size}`;
                    } else if (param.field === 'department_name') {
                        queryCount = queryStr + ` ORDER BY department_name ASC`;
                        queryStr += ` ORDER BY department_name ASC LIMIT ${limit},${size}`;
                    } else if (param.field === 'position_name') {
                        queryCount = queryStr + ` ORDER BY position_name ASC`;
                        queryStr += ` ORDER BY position_name ASC LIMIT ${limit},${size}`;
                    }
                } else if (param.order === 'descend') {
                    if (param.field === 'score') {
                        queryCount = queryStr + ` ORDER BY score DESC`;
                        queryStr += ` ORDER BY score DESC LIMIT ${limit},${size}`;
                    } else if (param.field === 'work_unit_name') {
                        queryCount = queryStr + ` ORDER BY work_unit_name DESC`;
                        queryStr += ` ORDER BY work_unit_name DESC LIMIT ${limit},${size}`;
                    } else if (param.field === 'department_name') {
                        queryCount = queryStr + ` ORDER BY department_name DESC`;
                        queryStr += ` ORDER BY department_name DESC LIMIT ${limit},${size}`;
                    } else if (param.field === 'position_name') {
                        queryCount = queryStr + ` ORDER BY position_name DESC`;
                        queryStr += ` ORDER BY position_name DESC LIMIT ${limit},${size}`;
                    }
                } else {
                    throw new Error(JSON.stringify({ code: 2, msg: '无效的排序方式' }));
                }
            } else {
                queryCount = queryStr + ' ORDER BY score DESC';
                queryStr += ` ORDER BY score DESC LIMIT ${limit},${size}`;
            }
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: submitData
            })
            const count: any = await this.ctx.model.query(queryCount, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: submitData
            })
            if (result.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            return { count: count.length, employee_list: result };
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找谁评的分详情列表
     * @param param 
     */
    public async searchDetailListByEmployee(param: any) {
        try {
            const project_id = param.project_id;
            const employee_id = param.employee_id;
            const queryStr = `select e.employee_id,e.employee_name,w.work_unit_name,d.department_name,p.position_name,temp.created_at,temp.score from 
            (select employee_id,sum(grade_result_score) as score,created_at from t_grade_result where project_id=:project_id and target_employee_id=:employee_id group by employee_id) temp 
            inner join t_employee e on temp.employee_id=e.employee_id and e.is_delete='0' inner join t_work_unit w on e.work_unit_id=w.work_unit_id and w.is_delete='0' 
            inner join t_department d on e.department_id=d.department_id and d.is_delete='0' inner join t_position p on e.position_id=p.position_id and p.is_delete='0'`;
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { project_id, employee_id }
            })
            if (result.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            return result;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找他的打分详情列表
     * @param param 
     */
    public async searchDetailListByTargetEmployee(param: any) {
        try {
            const project_id = param.project_id;
            const employee_id = param.employee_id;
            const queryStr = `select e.employee_id,e.employee_name,w.work_unit_name,d.department_name,p.position_name,temp.created_at,temp.score from 
            (select target_employee_id as employee_id,sum(grade_result_score) as score,created_at from t_grade_result where project_id=:project_id and employee_id=:employee_id group by target_employee_id) temp 
            inner join t_employee e on temp.employee_id=e.employee_id and e.is_delete='0' inner join t_work_unit w on e.work_unit_id=w.work_unit_id and w.is_delete='0' 
            inner join t_department d on e.department_id=d.department_id and d.is_delete='0' inner join t_position p on e.position_id=p.position_id and p.is_delete='0'`;
            const result: any = await this.ctx.model.query(queryStr, {
                type: this.ctx.model.QueryTypes.SELECT,
                replacements: { project_id, employee_id }
            })
            if (result.length <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
            }
            return result;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 根据结束时间结束考核项目
     */
    public async endProject() {
        try {
            const date = new Date();
            const dateNow = date.toLocaleString();
            const Op = this.ctx.model.Sequelize.Op;
            const result: any = await this.ctx.model.Project.findAll<Project>({ attributes: ['project_id', 'project_end_time'], where: { project_status: { [Op.ne]: '4' }, is_delete: '0' } });
            if (result.length > 0) {
                for (let i = 0; i < result.length; i++) {
                    const project_end_time = result[i].project_end_time;
                    const minutes = this.ctx.helper.getMinutes(project_end_time, dateNow);
                    if (minutes > 0) {
                        const project_id = result[i].project_id;
                        await this.ctx.model.Project.update({ project_status: '4' }, { where: { project_id } });
                    }
                }
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 根据开始时间启动考核项目
     */
    public async startProject() {
        try {
            const date = new Date();
            const dateNow = date.toLocaleString();
            const result: any = await this.ctx.model.Project.findAll<Project>({ attributes: ['project_id', 'project_start_time'], where: { project_status: '1', is_delete: '0' } });
            if (result.length > 0) {
                for (let i = 0; i < result.length; i++) {
                    const project_start_time = result[i].project_start_time;
                    const minutes = this.ctx.helper.getMinutes(project_start_time, dateNow);
                    if (minutes > 0) {
                        const project_id = result[i].project_id;
                        await this.ctx.model.Project.update({ project_status: '2' }, { where: { project_id } });
                    }
                }
            }
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 随机查找项目模板
     */
    public async searchProjectTemplet() {
        try {
            const result: any = await this.ctx.model.Project.findOne<Project>({
                attributes: ['project_id', 'project_name', 'project_rule', 'project_min', 'project_max', 'project_is_self', 'project_start_time', 'project_end_time'],
                where: { is_delete: '0' }
            })
            if (!result) {
                throw new Error(JSON.stringify({ code: 2, msg: '当前没有可用的项目模板' }));
            }
            const { project_id, project_name, project_rule, project_min, project_max, project_is_self, project_end_time, project_start_time } = result;
            const queryEmployee=`select g.employee_id from t_grade_employee g inner join t_employee e on g.employee_id=e.employee_id and e.employee_status='2' and e.is_delete='0' 
            where g.project_id=:project_id and g.is_delete='0'`;
            const employee: any = await this.ctx.model.query(queryEmployee,{
                type:this.ctx.model.QueryTypes.SELECT,
                replacements:{project_id}
            });
            const queryTargetEmployee=`select g.employee_id from t_grade_employee g inner join t_employee e on g.employee_id=e.employee_id and e.employee_status='2' and e.is_delete='0' 
            where g.project_id=:project_id and g.is_delete='0'`;
            const target_employee: any = await this.ctx.model.query(queryTargetEmployee,{
                type:this.ctx.model.QueryTypes.SELECT,
                replacements:{project_id}
            });
            const grade_rule: any = await this.ctx.model.GradeRule.findAll<GradeRule>({
                attributes: ['position_id', 'project_score'],
                where: { project_id, is_delete: '0' }
            })
            const returnData: any = {};
            returnData.project_name = project_name;
            returnData.project_rule = project_rule;
            returnData.project_min = project_min;
            returnData.project_max = project_max;
            returnData.project_end_time = project_end_time;
            returnData.project_start_time = project_start_time;
            returnData.project_is_self = project_is_self;
            returnData.employee = employee;
            returnData.target_employee = target_employee;
            returnData.position_list = grade_rule;
            return returnData;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 判断日期是否符合要求
     * @param param 
     */
    public async checkDate(param: any) {
        try {
            let checkResult = true;
            const { project_id, project_start_time } = param;
            const result: any = await this.ctx.model.Project.findAll<Project>({ attributes: ['project_id', 'project_end_time'], where: { is_delete: '0' } });
            for (let i = 0; i < result.length; i++) {
                const target_project_id = result[i].project_id;
                const project_end_time = result[i].project_end_time;
                if (project_id && project_id.length > 0) {
                    if (project_id === target_project_id) {
                        continue;
                    }
                }
                const minutes = this.ctx.helper.getMinutes(project_end_time, project_start_time);
                if (minutes < 0) {
                    checkResult = false;
                    break;
                }
            }
            return checkResult;
        } catch (e) {
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找剩余考核人列表
     * @param param 
     */
    public async searchSurplus(param:any){
        try{
            const {project_id}=param;
            const queryStr=`
                    SELECT
                        e.employee_id,
                        e.employee_name,
                        e.employee_account,
                        w.work_unit_name,
                        d.department_name,
                        p.position_name 
                    FROM
                        t_grade_employee g
                        INNER JOIN t_employee e ON g.employee_id = e.employee_id 
                        AND g.is_delete = '0' 
                        AND e.is_delete = '0' 
                        AND e.employee_status = '2'
                        INNER JOIN t_work_unit w ON e.work_unit_id = w.work_unit_id 
                        AND w.is_delete = '0'
                        INNER JOIN t_department d ON e.department_id = d.department_id 
                        AND d.is_delete = '0'
                        INNER JOIN t_position p ON e.position_id = p.position_id 
                        AND p.is_delete = '0' 
                    WHERE
                        g.project_id =:project_id 
                        AND g.employee_id NOT IN (
                    SELECT
                        g.employee_id 
                    FROM
                        t_grade_result g 
                    WHERE
                        g.project_id =:project_id 
                        AND g.is_delete = '0' 
                    GROUP BY
                        g.employee_id 
                    HAVING
                        sum( g.grade_result_score ) > 0 
                        )`;
            const result:any=await this.ctx.model.query(queryStr,{
                type:this.ctx.model.QueryTypes.SELECT,
                replacements:{project_id}
            });
            if(result.length<=0){
                throw new Error(JSON.stringify({code:2,msg:'没有数据'}));
            }
            return result;
        }catch(e){
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

    /**
     * 查找已经考核人列表
     * @param param 
     */
    public async searchHasGradedList(param:any){
        try{
            const {project_id}=param;
            const queryStr=`
                    SELECT
                        e.employee_id,
                        e.employee_name,
                        e.employee_account,
                        w.work_unit_name,
                        d.department_name,
                        p.position_name 
                    FROM
                        t_grade_result g
                        INNER JOIN t_employee e ON g.employee_id = e.employee_id 
                        AND e.is_delete = '0' 
                        AND e.employee_status = '2'
                        INNER JOIN t_work_unit w ON e.work_unit_id = w.work_unit_id 
                        AND w.is_delete = '0'
                        INNER JOIN t_department d ON e.department_id = d.department_id 
                        AND d.is_delete = '0'
                        INNER JOIN t_position p ON e.position_id = p.position_id 
                        AND p.is_delete = '0' 
                    WHERE
                        g.project_id = :project_id 
                        AND g.is_delete = '0' 
                    GROUP BY
                        g.employee_id 
                    HAVING
                        sum( g.grade_result_score ) >0`;
            const result:any=await this.ctx.model.query(queryStr,{
                type:this.ctx.model.QueryTypes.SELECT,
                replacements:{project_id}
            });
            if(result.length<=0){
                throw new Error(JSON.stringify({code:2,msg:'没有数据'}));
            }
            return result;
        }catch(e){
            this.ctx.logger.error(e);
            throw new Error(e.message);
        }
    }

}